<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Model_job extends CI_Model {

	public function __construct(){
		parent::__construct();
	}

	public function add_job(){
		$data = array(
			'id_dn' => $this->session->userdata('id'),
			'ten_cv' => $this->input->post('ten'),
			'dia_diem' => $this->input->post('diachi'),
			'hinhthuc_tt' => $this->input->post('hinhthuc_tt'),
			'luong' => $this->session->userdata('luong'),
			'quyen_loi' => $this->input->post('quyen_loi'),
			'chi_tiet' => $this->input->post('mota'),
			'yeucau' => $this->input->post('yeucau'),
			'hinhthuc_nop' => implode("-", $this->input->post('hinhthuc_hs')),
			'ngay_bd' => date("Y-m-d"),
			'ngay_kt' => $this->input->post('ngay_kt'),
			'ho_so' => $this->session->userdata('hoso'),
			'file' => $this->session->flashdata('message'),
			'congviec_bd' => $this->input->post('congviec_bd'),
			'congviec_kt' => $this->input->post('congviec_kt'),
			'active' =>0
			);
		$query = $this->db->insert('congviec_tt', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function save_load(){
		$data = array(
			'id_dn' => $this->session->userdata('id'),
			'ten_cv' => $this->input->post('ten'),
			'dia_diem' => $this->input->post('diachi'),
			'hinhthuc_tt' => $this->input->post('hinhthuc_tt'),
			'luong' => $this->session->userdata('luong'),
			'quyen_loi' => $this->input->post('quyen_loi'),
			'chi_tiet' => $this->input->post('mota'),
			'yeucau' => $this->input->post('yeucau'),
			'hinhthuc_nop' => implode("-", $this->input->post('hinhthuc_hs')),
			'ngay_bd' => date("Y-m-d"),
			'ngay_kt' => $this->input->post('ngay_kt'),
			'ho_so' => $this->session->userdata('hoso'),
			'file' => $this->session->flashdata('message'),
			'congviec_bd' => $this->input->post('congviec_bd'),
			'congviec_kt' => $this->input->post('congviec_kt'),
			'active' =>1
			);
		$query = $this->db->insert('congviec_tt', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function list_job($id_dn) {
		// $this->db->where('id_dn', $id_dn);
		// $query = $this->db->get('congviec_tt');
		// return $query->result_array();
		$query = $this->db->query('select *, (select count(*) from ung_tuyen where congviec_tt.id=ung_tuyen.id_cv) as count from congviec_tt where id_dn='.$id_dn);
		return $query->result_array();	
	}

	public function home_job($total, $start){
		$this->db->select('*');
		$this->db->from('congviec_tt');
		$this->db->where('active', 1);
		$this->db->order_by('ngay_bd desc, ten_cv asc');
		$this->db->join('thongtin_dn', 'congviec_tt.id_dn = thongtin_dn.id_dn', 'left');
		$this->db->limit($total, $start);
		$query = $this->db->get();
		return $query->result_array();
	}

	public function countAll(){
		$this->db->where('active', 1);
		$this->db->from('congviec_tt');
		return $this->db->count_all_results();
	}

	public function job_detail($id){
		$this->db->where('id', $id);
		$query = $this->db->get('congviec_tt');
		return $query->row_array();
	}

	public function active_job($id){
		$data = array('active' => 1);
		$this->db->where('id', $id);
		$query = $this->db->update('congviec_tt', $data);
		if($query){
			return true;
		}else{
			return false;
		}
	}

	public function deactive_job($id){
		$data = array('active' => 0);
		$this->db->where('id', $id);
		$query = $this->db->update('congviec_tt', $data);
		if($query){
			return true;
		}else{
			return false;
		}
	}

	public function delete_job($id_cv){
		$this->db->where('id', $id_cv);
		$query = $this->db->delete('congviec_tt');
		if($query){
			return true;
		}else{
			return false;
		}
	}

	public function edit_job($id_cv){
		$data = array(
			'ten_cv' => $this->input->post('ten'),
			'dia_diem' => $this->input->post('diachi'),
			'hinhthuc_tt' => $this->input->post('hinhthuc_tt'),
			'luong' => $this->session->userdata('luong'),
			'quyen_loi' => $this->input->post('quyen_loi'),
			'chi_tiet' => $this->input->post('mota'),
			'yeucau' => $this->input->post('yeucau'),
			'hinhthuc_nop' => implode("-", $this->input->post('hinhthuc_hs')),
			'ngay_bd' => date("Y-m-d"),
			'ngay_kt' => $this->input->post('ngay_kt'),
			'ho_so' => $this->session->userdata('hoso'),
			'file' => $this->session->flashdata('message'),
			'congviec_bd' => $this->input->post('congviec_bd'),
			'congviec_kt' => $this->input->post('congviec_kt')
			);
		$this->db->where('id', $id_cv);
		$query = $this->db->update('congviec_tt', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function list_apply($id_cv){
		$this->db->select('t1.*, t2.*');
		$this->db->from('thongtin_sv as t1, ung_tuyen as t2');
		$this->db->where('t1.mssv = t2.mssv');
		$this->db->where('t2.id_cv', $id_cv);
		$this->db->order_by('t1.mssv', 'asc');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function apply_detail($id_cv, $mssv)
	{
		$this->db->select('t1.*, t2.*');
		$this->db->from('thongtin_sv as t1, ung_tuyen as t2');
		$this->db->where('t1.mssv = t2.mssv');
		$this->db->where('t2.id_cv', $id_cv);
		$this->db->where('t2.mssv', $mssv);
		$query = $this->db->get();
		return $query->row_array();
	}

	public function show_cv($id_cv, $mssv){
		$this->db->select('t1.*');
		$this->db->from('ho_so as t1, ung_tuyen as t2');
		$this->db->where('t1.id = t2.id_hs');
		// $this->db->where('t1.mssv = t2.mssv');
		$this->db->where('t2.mssv', $mssv);
		$this->db->where('t2.id_cv', $id_cv);
		$query = $this->db->get();
		return $query->row_array();
	}

	public function student_pass($id_cv, $mssv){
		$data = array('trangthai_dn' => 1);
		$this->db->where('id_cv', $id_cv);
		$this->db->where('mssv', $mssv);
		$query = $this->db->update('ung_tuyen', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function student_fail($id_cv, $mssv){
		$data = array('trangthai_dn' => 0);
		$this->db->where('id_cv', $id_cv);
		$this->db->where('mssv', $mssv);
		$query = $this->db->update('ung_tuyen', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function check_comment($id_cv, $mssv){
		$data = array(
			'id_cv' => $id_cv,
			'mssv' => $mssv,
			'trangthai_dn' => 1,
			'trangthai_sv' => 1
			);
		$this->db->where($data);
		$query = $this->db->get('ung_tuyen');
		return $query->row_array();
	}

	public function get_comment($id_cv, $mssv)
	{
		$this->db->select('nx.*, nv.ten, dn.ten_cty');
		$this->db->from('nhan_xet as nx, thongtin_nv as nv, thongtin_dn as dn');
		$this->db->where('nx.id_cv', $id_cv);
		$this->db->where('nx.mssv', $mssv);
		$this->db->where('nx.id_nv = nv.id');
		$this->db->where('nv.id_dn = dn.id_dn');
		$query = $this->db->get();
		return $query->result_array();
		// $this->db->where('id_cv', $id_cv);
		// $this->db->where('mssv', $mssv);
		// $query = $this->db->get('nhan_xet');
		// return $query->result_array();
	}

	public function add_comment(){
		$data = array(
			'id_cv' => $this->input->post('id_cv'),
			'id_nv' => $this->session->userdata('id'),
			'mssv' => $this->input->post('mssv'),
			'ngay_nx' => date("Y-m-d H:i:s"),
			'thoi_gian' => "Từ ".$this->input->post('bd')." Đến ".$this->input->post('kt'),
			'task' => $this->input->post('task'),
			'ti_le' => $this->input->post('amountRange')."%",
			'nhan_xet' => $this->input->post('nhan_xet')
			);
		$query = $this->db->insert('nhan_xet', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function edit_comment($id){
		$data = array(
			'id_cv' => $this->input->post('id_cv'),
			'id_nv' => $this->session->userdata('id'),
			'mssv' => $this->input->post('mssv'),
			'ngay_nx' => date("Y-m-d H:i:s"),
			'thoi_gian' => "Từ ".$this->input->post('bd')." Đến ".$this->input->post('kt'),
			'task' => $this->input->post('task'),
			'ti_le' => $this->input->post('amountRange')."%",
			'nhan_xet' => $this->input->post('nhan_xet')
			);
		$this->db->where('id', $id);
		$query = $this->db->update('nhan_xet', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function delete_comment($id){
		$this->db->where('id', $id);
		$query = $this->db->delete('nhan_xet');
		if($query){
			return true;
		}else{
			return false;
		}
	}

	public function delete_candidate($id_cv, $mssv){
		$this->db->where('id_cv', $id_cv);
		$this->db->where('mssv', $mssv);
		$query = $this->db->delete('ung_tuyen');
		if($query){
			return true;
		}else{
			return false;
		}
	}

	public function list_job_apply(){
		$query = $this->db->query('select cv.ten_cv, cv.dia_diem, cv.congviec_bd, cv.congviec_kt, dn.ten_cty, dn.id_dn, ut.trangthai_dn, ut.trangthai_sv, ut.mssv, ut.id_cv,
								   (select count(*) from ung_tuyen where id_cv=cv.id) as count
								   from ung_tuyen ut, congviec_tt cv, thongtin_dn dn, thongtin_sv sv
								   where ut.id_cv = cv.id
								   and cv.id_dn = dn.id_dn
								   and ut.mssv =sv.mssv
								   and sv.id_dn ='.$this->session->userdata('id').' order by dn.ten_cty asc');
		return $query->result_array();
	}

	public function check_confirm($id_cv, $mssv){
		$data = array(
			'id_cv' => $id_cv,
			'mssv' => $mssv,
			'trangthai_dn' => 1
			);
		$this->db->where($data);
		$query = $this->db->get('ung_tuyen');
		return $query->row_array();
	}

	public function confirm_job($id_cv, $mssv){
		$data = array('trangthai_sv' => 1);
		$this->db->where('id_cv', $id_cv);
		$this->db->where('mssv', $mssv);
		$query = $this->db->update('ung_tuyen', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function cancel_another_job($id_cv, $mssv){
		$data = array('trangthai_sv' => 0);
		$this->db->where('id_cv !=', $id_cv);
		$this->db->where('mssv', $mssv);
		$query = $this->db->update('ung_tuyen', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function cancel_confirm_job($id_cv, $mssv){
		$data = array('trangthai_sv' => 0);
		$this->db->where('id_cv', $id_cv);
		$this->db->where('mssv', $mssv);
		$query = $this->db->update('ung_tuyen', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function list_job_admin($mssv){
		$query = $this->db->query('select cv.ten_cv, cv.dia_diem, dn.ten_cty, ut.trangthai_dn, ut.trangthai_sv, ut.mssv, ut.id_cv, dn.id_dn, hs.id as id_hs, hs.ten_hs,
								   (select count(*) from ung_tuyen where id_cv=cv.id) as count
								   from ung_tuyen ut, congviec_tt cv, thongtin_dn dn, thongtin_sv sv, ho_so hs
								   where ut.id_cv = cv.id
								   and cv.id_dn = dn.id_dn
								   and ut.mssv = sv.mssv
								   and ut.id_hs = hs.id 
								   and sv.mssv ='.$mssv.' order by dn.ten_cty asc');
		return $query->result_array();
	}

	public function list_apply_admin($id_cv){
		$this->db->select('t1.*, t2.*, t3.ten_hs');
		$this->db->from('thongtin_sv as t1, ung_tuyen as t2, ho_so t3');
		$this->db->where('t1.mssv = t2.mssv');
		$this->db->where('t2.id_hs = t3.id');
		$this->db->where('t2.mssv = t3.mssv');
		$this->db->where('t2.id_cv', $id_cv);
		$this->db->order_by('t1.mssv', 'asc');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function check_report($id_dn, $bd, $kt){
		$data = array(
			'id_dn' => $id_dn,
			'bat_dau' => $bd,
			'ket_thuc' => $kt
			);
		$this->db->where($data);
		$query = $this->db->get('thong_ke');
		return $query->row_array();
	}

	public function add_report($id_dn, $bd, $kt){
		$data = array(
			'id_dn' => $id_dn,
			'bat_dau' => $bd,
			'ket_thuc' => $kt,
			'cong_viec' =>1
			);
		$query = $this->db->insert('thong_ke', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}
	public function delete_report($id_dn, $bd, $kt){
		$this->db->where('id_dn', $id_dn);
		$this->db->where('bat_dau', $bd);
		$this->db->where('ket_thuc', $kt);
		$query = $this->db->delete('thong_ke');
		if($query){
			return true;
		}else{
			return false;
		}
	}

	public function add_report_job($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set cong_viec=cong_viec+1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function delete_report_job($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set cong_viec=cong_viec-1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function add_report_candidate($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set ung_vien=ung_vien+1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function delete_report_candidate($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set ung_vien=ung_vien-1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function add_report_interview($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set interview=interview+1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function delete_report_interview($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set interview=interview-1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function add_report_confirm($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set confirm=confirm+1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function delete_report_confirm($id_dn, $bd, $kt){
		$query = $this->db->query("update thong_ke set confirm=confirm-1 where id_dn='".$id_dn."' and bat_dau='".$bd."' and ket_thuc='".$kt."'");
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function show_report($id_dn){
		$this->db->where('id_dn', $id_dn);
		$query = $this->db->get('thong_ke');
		return $query->result_array();
	}

	public function list_job_report($id_dn, $bd, $kt){
		$query = $this->db->query("select *, (select count(*) from ung_tuyen where congviec_tt.id=ung_tuyen.id_cv) as count from congviec_tt where id_dn=".$id_dn." and congviec_bd='".$bd."' and congviec_kt='".$kt."'");
		return $query->result_array();
	}

	public function search_job($key_search){
		$this->db->select('*');
		$this->db->from('congviec_tt');
		$this->db->where("match(ten_cv, dia_diem, quyen_loi, chi_tiet, yeucau) against('".$key_search."') and active=1");
		$this->db->order_by('ngay_bd desc, ten_cv asc');
		$this->db->join('thongtin_dn', 'congviec_tt.id_dn = thongtin_dn.id_dn', 'left');
		$query = $this->db->get();
		return $query->result_array();
	}

	public function all_record(){
		$query = $this->db->get('ho_so');
		return $query->result_array();
	}

	public function search_record($key_search){
		$this->db->where('mssv', $key_search);
		$query = $this->db->get('ho_so');
		return $query->result_array();
	}

	public function check_date_comment($mssv){
		$query = $this->db->query("select thoi_gian from nhan_xet where id in (select max(id) from nhan_xet where mssv='".$mssv."') and mssv='".$mssv."'");
		if($query) {
			return $query->row_array();
			return true;
		}else {
			return false;
		}
	}

	public function add_leader_ajax($mssv, $id_nv){
		$data = array(
			'id_nv' => $id_nv
			);
		$this->db->where('mssv', $mssv);
		$this->db->where('trangthai_dn', 1);
		$this->db->where('trangthai_sv', 1);
		$query = $this->db->update('ung_tuyen', $data);
		if($query) {
			return true;
		}else {
			return false;
		}
	}

	public function list_job_guide($id_nv){
		$query = $this->db->query("select distinct cv.id, cv.ten_cv, (select count(*) from ung_tuyen as ut where ut.id_nv=".$id_nv." and ut.id_cv=cv.id) as count
									from congviec_tt as cv, ung_tuyen as ut
									where ut.id_cv=cv.id and ut.id_nv=".$id_nv);
		if($query) {
			return $query->result_array();
			return true;
		}else {
			return false;
		}
	}
}